from pyecharts.charts import Page
import pandas as pd
import sys
sys.path.append("..//database")
from SqlWizard import *
from CmnFuncs import *

def run_meal():
    '''
    本模块包含品种：豆粕、菜粕、玉米，关联品种：豆油、菜油、淀粉
    设计图表：
    加入豆一
    1. 豆粕现货基差表；
    2. 豆粕月差表；
    3. 豆粕 / 豆油比价
    4. 菜粕现货基差表；
    5. 菜粕月差表；
    6. 菜粕 / 菜油比价
    7. 玉米现货基差表；
    8. 玉米月差表；
    9. 淀粉现货基差表；
    10. 淀粉月差表；
    11. 玉米 / 淀粉比价
    12. 豆粕 / 菜粕 比价

    '''

    a_list = date_checker_1()

    a_spot = read_sql_spot('spot_a')
    a_index = read_sql_fut_index('fut_index_a')
    a_ry = read_sql_ry('roll_yield_a')
    a_spdrdx = read_sql_spdrdx('spdrdex_a')
    a_1 = read_sql_fut('fut_a{}'.format(a_list[0]))
    a_2 = read_sql_fut('fut_a{}'.format(a_list[1]))
    a_3 = read_sql_fut('fut_a{}'.format(a_list[2]))

    m_spot = read_sql_spot('spot_m')
    m_index = read_sql_fut_index('fut_index_m')
    m_ry = read_sql_ry('roll_yield_m')
    m_spdrdx = read_sql_spdrdx('spdrdex_m')
    m_1 = read_sql_fut('fut_m{}'.format(a_list[0]))
    m_2 = read_sql_fut('fut_m{}'.format(a_list[1]))
    m_3 = read_sql_fut('fut_m{}'.format(a_list[2]))

    rm_spot = read_sql_spot('spot_rm')
    rm_index = read_sql_fut_index('fut_index_rm')
    rm_ry = read_sql_ry('roll_yield_rm')
    rm_spdrdx = read_sql_spdrdx('spdrdex_rm')
    rm_1 = read_sql_fut('fut_rm{}'.format(a_list[0]))
    rm_2 = read_sql_fut('fut_rm{}'.format(a_list[1]))
    rm_3 = read_sql_fut('fut_rm{}'.format(a_list[2]))

    c_spot = read_sql_spot('spot_c')
    c_index = read_sql_fut_index('fut_index_c')
    c_ry = read_sql_ry('roll_yield_c')
    c_spdrdx = read_sql_spdrdx('spdrdex_c')
    c_1 = read_sql_fut('fut_c{}'.format(a_list[0]))
    c_2 = read_sql_fut('fut_c{}'.format(a_list[1]))
    c_3 = read_sql_fut('fut_c{}'.format(a_list[2]))

    y_spot = read_sql_spot('spot_y')
    y_index = read_sql_fut_index('fut_index_y')
    y_1 = read_sql_fut('fut_y{}'.format(a_list[0]))
    y_2 = read_sql_fut('fut_y{}'.format(a_list[1]))
    y_3 = read_sql_fut('fut_y{}'.format(a_list[2]))

    oi_spot = read_sql_spot('spot_oi')
    oi_index = read_sql_fut_index('fut_index_oi')
    oi_1 = read_sql_fut('fut_oi{}'.format(a_list[0]))
    oi_2 = read_sql_fut('fut_oi{}'.format(a_list[1]))
    oi_3 = read_sql_fut('fut_oi{}'.format(a_list[2]))

    cs_spot = read_sql_spot('spot_cs')
    cs_index = read_sql_fut_index('fut_index_cs')
    cs_ry = read_sql_ry('roll_yield_cs')
    cs_spdrdx = read_sql_spdrdx('spdrdex_cs')
    cs_1 = read_sql_fut('fut_cs{}'.format(a_list[0]))
    cs_2 = read_sql_fut('fut_cs{}'.format(a_list[1]))
    cs_3 = read_sql_fut('fut_cs{}'.format(a_list[2]))

    # 豆一
    header_a = draw_separator('豆一')
    # 豆一远期曲线
    future_curve_a = draw_future_curve(a_spot, a_1, a_2, a_3)
    # 豆一一致性预期指标
    idctr_a = draw_spec_idctr('豆一', a_spdrdx, a_index, a_ry)
    # 1. 豆一现货基差表；
    a_spot_chart = draw_spot_spd('豆一', a_spot)
    # 豆一合约价格、成交量、持仓量、持仓量变化图
    a_contract_1 = draw_contract('a{}'.format(a_list[0]), a_1)
    a_contract_2 = draw_contract('a{}'.format(a_list[1]), a_2)
    a_contract_3 = draw_contract('a{}'.format(a_list[2]), a_3)
    # 2. 豆一月差表；
    a_12_chart = draw_spd('a{}'.format(a_list[0]), a_1['close'], 'a{}'.format(a_list[1]), a_2['close'])
    a_23_chart = draw_spd('a{}'.format(a_list[1]), a_2['close'], 'a{}'.format(a_list[2]), a_3['close'])

    # 豆粕
    header_m = draw_separator('豆粕')
    # 豆粕远期曲线
    future_curve_m = draw_future_curve(m_spot, m_1, m_2, m_3)
    # 豆粕一致性预期指标
    idctr_m = draw_spec_idctr('豆粕', m_spdrdx, m_index, m_ry)
    # 1. 豆粕现货基差表；
    m_spot_chart = draw_spot_spd('豆粕', m_spot)
    # 豆粕合约价格、成交量、持仓量、持仓量变化图
    m_contract_1 = draw_contract('m{}'.format(a_list[0]), m_1)
    m_contract_2 = draw_contract('m{}'.format(a_list[1]), m_2)
    m_contract_3 = draw_contract('m{}'.format(a_list[2]), m_3)
    # 2. 豆粕月差表；
    m_12_chart = draw_spd('m{}'.format(a_list[0]), m_1['close'], 'm{}'.format(a_list[1]), m_2['close'])
    m_23_chart = draw_spd('m{}'.format(a_list[1]), m_2['close'], 'm{}'.format(a_list[2]), m_3['close'])

    # 菜粕
    header_rm = draw_separator('菜粕')
    # 菜粕远期曲线
    future_curve_rm = draw_future_curve(rm_spot, rm_1, rm_2, rm_3)
    # 菜粕一致性预期指标
    idctr_rm = draw_spec_idctr('菜粕', rm_spdrdx, rm_index, rm_ry)
    # 4. 菜粕现货基差表；
    rm_spot_chart = draw_spot_spd('菜粕', rm_spot)
    # 菜粕合约价格、成交量、持仓量、持仓量变化图
    rm_contract_1 = draw_contract('rm{}'.format(a_list[0]), rm_1)
    rm_contract_2 = draw_contract('rm{}'.format(a_list[1]), rm_2)
    rm_contract_3 = draw_contract('rm{}'.format(a_list[2]), rm_3)
    # 5. 菜粕月差表；
    rm_12_chart = draw_spd('rm{}'.format(a_list[0]), rm_1['close'], 'rm{}'.format(a_list[1]), rm_2['close'])
    rm_23_chart = draw_spd('rm{}'.format(a_list[1]), rm_2['close'], 'rm{}'.format(a_list[2]), rm_3['close'])

    # 玉米
    header_c = draw_separator('玉米')
    # 玉米远期曲线
    future_curve_c = draw_future_curve(c_spot, c_1, c_2, c_3)
    # 玉米一致性预期指标
    idctr_c = draw_spec_idctr('玉米', c_spdrdx, c_index, c_ry)
    # 7. 玉米现货基差表；
    c_spot_chart = draw_spot_spd('玉米', c_spot)
    # 玉米合约价格、成交量、持仓量、持仓量变化图
    c_contract_1 = draw_contract('c{}'.format(a_list[0]), c_1)
    c_contract_2 = draw_contract('c{}'.format(a_list[1]), c_2)
    c_contract_3 = draw_contract('c{}'.format(a_list[2]), c_3)
    # 8. 玉米月差表；
    c_12_chart = draw_spd('c{}'.format(a_list[0]), c_1['close'], 'c{}'.format(a_list[1]), c_2['close'])
    c_23_chart = draw_spd('c{}'.format(a_list[1]), c_2['close'], 'c{}'.format(a_list[2]), c_3['close'])

    # 淀粉
    header_cs = draw_separator('淀粉')
    # 淀粉远期曲线
    future_curve_cs = draw_future_curve(cs_spot, cs_1, cs_2, cs_3)
    # 淀粉一致性预期指标
    idctr_cs = draw_spec_idctr('淀粉', cs_spdrdx, cs_index, cs_ry)
    # 9. 淀粉现货基差表；
    cs_spot_chart = draw_spot_spd('淀粉', cs_spot)
    # 淀粉合约价格、成交量、持仓量、持仓量变化图
    cs_contract_1 = draw_contract('cs{}'.format(a_list[0]), cs_1)
    cs_contract_2 = draw_contract('cs{}'.format(a_list[1]), cs_2)
    cs_contract_3 = draw_contract('cs{}'.format(a_list[2]), cs_3)
    # 10. 淀粉月差表；
    cs_12_chart = draw_spd('cs{}'.format(a_list[0]), cs_1['close'], 'cs{}'.format(a_list[1]), cs_2['close'])
    cs_23_chart = draw_spd('cs{}'.format(a_list[1]), cs_2['close'], 'cs{}'.format(a_list[2]), cs_3['close'])

    # 品种间
    header_cross = draw_separator('品种间')

    # 3. 豆粕 / 豆油比价
    arbi_record('m', m_index['close'], 'y', y_index['close'], x=1, y=1, opt=1, cat=1)
    arbi_record('m', m_spot['spot'], 'y', y_spot['spot'], x=1, y=1, opt=1, cat=2)
    ratio_m_y_spot = draw_price_ratio('豆粕现货', m_spot['spot'], '豆油现货', y_spot['spot'], 3)
    ratio_m_y_main = draw_price_ratio('豆粕主连', m_spot['main_price'], '豆油主连', y_spot['main_price'], 3)
    ratio_m_y_1 = draw_price_ratio('m{}'.format(a_list[0]), m_1['close'], 'y{}'.format(a_list[0]), y_1['close'], 3)
    ratio_m_y_2 = draw_price_ratio('m{}'.format(a_list[1]), m_2['close'], 'y{}'.format(a_list[1]), y_2['close'], 3)
    ratio_m_y_3 = draw_price_ratio('m{}'.format(a_list[2]), m_3['close'], 'y{}'.format(a_list[2]), y_3['close'], 3)

    # 6. 菜粕 / 菜油比价
    arbi_record('rm', rm_index['close'], 'oi', oi_index['close'], x=1, y=1, opt=1, cat=1)
    arbi_record('rm', rm_spot['spot'], 'oi', oi_spot['spot'], x=1, y=1, opt=1, cat=2)
    ratio_rm_oi_spot = draw_price_ratio('菜粕现货', rm_spot['spot'], '菜油现货', oi_spot['spot'], 4)
    ratio_rm_oi_main = draw_price_ratio('菜粕主连', rm_spot['main_price'], '菜油主连', oi_spot['main_price'], 4)
    ratio_rm_oi_1 = draw_price_ratio('rm{}'.format(a_list[0]), rm_1['close'], 'oi{}'.format(a_list[0]), oi_1['close'], 4)
    ratio_rm_oi_2 = draw_price_ratio('rm{}'.format(a_list[1]), rm_2['close'], 'oi{}'.format(a_list[1]), oi_2['close'], 4)
    ratio_rm_oi_3 = draw_price_ratio('rm{}'.format(a_list[2]), rm_3['close'], 'oi{}'.format(a_list[2]), oi_3['close'], 4)

    # 11. 玉米 / 淀粉比价
    arbi_record('c', c_index['close'], 'cs', cs_index['close'], x=1, y=1, opt=1, cat=1)
    arbi_record('c', c_spot['spot'], 'cs', cs_spot['spot'], x=1, y=1, opt=1, cat=2)
    ratio_c_cs_spot = draw_price_ratio('玉米现货', c_spot['spot'], '淀粉现货', cs_spot['spot'], 2)
    ratio_c_cs_main = draw_price_ratio('玉米主连', c_spot['main_price'], '淀粉主连', cs_spot['main_price'], 2)
    ratio_c_cs_1 = draw_price_ratio('c{}'.format(a_list[0]), c_1['close'], 'cs{}'.format(a_list[0]), cs_1['close'], 2)
    ratio_c_cs_2 = draw_price_ratio('c{}'.format(a_list[1]), c_2['close'], 'cs{}'.format(a_list[1]), cs_2['close'], 2)
    ratio_c_cs_3 = draw_price_ratio('c{}'.format(a_list[2]), c_3['close'], 'cs{}'.format(a_list[2]), cs_3['close'], 2)
    # 12. 豆粕 / 菜粕 价差
    arbi_record('m', m_index['close'], 'rm', rm_index['close'], x=1, y=1, opt=2, cat=1)
    arbi_record('m', m_spot['spot'], 'rm', rm_spot['spot'], x=1, y=1, opt=2, cat=2)
    diff_m_rm_spot = draw_price_dif('豆粕现货', m_spot['spot'], '菜粕现货', rm_spot['spot'], 5)
    diff_m_rm_main = draw_price_dif('豆粕主连', m_spot['main_price'], '菜粕主连', rm_spot['main_price'], 5)
    diff_m_rm_1 = draw_price_dif('m{}'.format(a_list[0]), m_1['close'], 'rm{}'.format(a_list[0]), rm_1['close'], 5)
    diff_m_rm_2 = draw_price_dif('m{}'.format(a_list[1]), m_2['close'], 'rm{}'.format(a_list[1]), rm_2['close'], 5)
    diff_m_rm_3 = draw_price_dif('m{}'.format(a_list[2]), m_3['close'], 'rm{}'.format(a_list[2]), rm_3['close'], 5)


    page = Page(page_title='粕类饲料链', layout=Page.SimplePageLayout)
    page.add(
        header_a,
        future_curve_a,
        idctr_a,
        a_spot_chart,
        a_contract_1,
        a_contract_2,
        a_contract_3,
        a_12_chart,
        a_23_chart,
        header_m,
        future_curve_m,
        idctr_m,
        m_spot_chart,
        m_contract_1,
        m_contract_2,
        m_contract_3,
        m_12_chart,
        m_23_chart,
        header_rm,
        future_curve_rm,
        idctr_rm,
        rm_spot_chart,
        rm_contract_1,
        rm_contract_2,
        rm_contract_3,
        rm_12_chart,
        rm_23_chart,
        header_c,
        future_curve_c,
        idctr_c,
        c_spot_chart,
        c_contract_1,
        c_contract_2,
        c_contract_3,
        c_12_chart,
        c_23_chart,
        header_cs,
        future_curve_cs,
        idctr_cs,
        cs_spot_chart,
        cs_contract_1,
        cs_contract_2,
        cs_contract_3,
        cs_12_chart,
        cs_23_chart,
        header_cross,
        ratio_m_y_spot,
        ratio_m_y_main,
        ratio_m_y_1,
        ratio_m_y_2,
        ratio_m_y_3,
        ratio_rm_oi_spot,
        ratio_rm_oi_main,
        ratio_rm_oi_1,
        ratio_rm_oi_2,
        ratio_rm_oi_3,
        ratio_c_cs_spot,
        ratio_c_cs_main,
        ratio_c_cs_1,
        ratio_c_cs_2,
        ratio_c_cs_3,
        diff_m_rm_spot,
        diff_m_rm_main,
        diff_m_rm_1,
        diff_m_rm_2,
        diff_m_rm_3,
    )
    page.render('粕类饲料链.html')
